-- =============================================
-- Procedure: GetAllInterestForMonthRedBal
-- Purpose:	This procedure computes the interest (reducing balance) on the loan db for the month supplied in the paramater @report_date
-- 			This procedure serves as a datasouce for the report
-- Author: Garret Stephenson
-- Created: 2013-5-10
-- Modification History
-- Modified By	Modification Date	Reason
-- =====================================================================================
--  Dale McFarlane    2014-2-7    Factor in early closed data of loans
-- =====================================================================================

--exec [GetAllInterestForMonthRedBal] '2013-10-05', 1
--select * from loan_addon

IF EXISTS (SELECT * FROM sysobjects WHERE id = object_id(N'[dbo].[GetAllInterestForMonthRedBal]') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
BEGIN
	DROP PROCEDURE dbo.[GetAllInterestForMonthRedBal]
END
GO

CREATE PROCEDURE [dbo].[GetAllInterestForMonthRedBal]
(
	@report_date DATETIME
)
AS
BEGIN
	DECLARE @month INT, @year INT
	SET @month = MONTH(@report_date)
	SET @year = YEAR(@report_date)

	SELECT * FROM
	(
		SELECT 
			L.product,
			L.term, 		 
			SUM
			(
				CASE 
					WHEN L.period = 'weekly' 
						THEN dbo.CalInterestWkly(@month, @year, L.principal, L.interest_rate * 100, L.start_date, L.closed_date, L.term, L.installment)
					WHEN L.period = 'bi-monthly'
						THEN dbo.CalInterestBiMnthly(@month, @year, L.principal, L.interest_rate * 100 , L.start_date, L.closed_date, L.term, L.installment)
					WHEN L.period = 'monthly' 
						THEN dbo.CalInterestMnthly(@month, @year, L.principal, L.interest_rate * 100, L.start_date, L.closed_date, L.term, L.installment)
				END
			) AS interest,
			L.interest_rate * 100 AS Interest_Rate
		FROM 
			loan_redbal L
		WHERE 
			((YEAR(L.end_date) = @year AND MONTH(L.end_date) >= @month) OR YEAR(L.end_date) > @year) AND ((YEAR(L.start_date) = @year AND MONTH(L.start_date) <= @month) OR YEAR(L.start_date) < @year) 
		GROUP BY 
			L.product, L.term, L.interest_rate
	) AS proj_interest 
	WHERE 
		proj_interest.interest > 0
END

GO


